package com.xin.auto.lib.base;

import android.content.Context;
import android.os.Environment;
import android.os.StatFs;
import android.util.Log;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelUtil {
    private static final String TAG = "ExcelUtil";
    //内存地址
    public static String root = Environment.getExternalStorageDirectory().getPath();
    private static File mFile;
    private static WritableSheet mSheet;

    private static volatile ExcelUtil mInstance;

    private ExcelUtil() {
    }

    public static ExcelUtil getInstance() {
        if (mInstance == null) {
            synchronized (ExcelUtil.class) {
                if (mInstance == null) {
                    mInstance = new ExcelUtil();
                }
            }
        }
        return mInstance;
    }

    public boolean exportExcel(Context context, UsbHelper usbHelper, UsbHelper.DownloadProgressListener listener) {
        try {
            File rootDir = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS).getAbsolutePath());
            String[] title = { "年份", "月份", "人数"};
            mFile = new File(rootDir, "人流量统计_" + CalendarUtils.getYear() + "_" + CalendarUtils.getMonth() + ".xls");
            if (!rootDir.exists()) {
                rootDir.mkdirs();
            } else {
                mFile.delete();
            }

            // 创建Excel工作表
            OutputStream os = new FileOutputStream(mFile);
            WritableWorkbook wwb = Workbook.createWorkbook(os);
            // 添加第一个工作表并设置第一个Sheet的名字
            mSheet = wwb.createSheet("人流量统计", 0);
            for (int i = 0; i < title.length; i++) {
                // Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
                // 在Label对象的子对象中指明单元格的位置和内容
                Label label = new Label(i, 0, title[i], getHeader());
                // 将定义好的单元格添加到工作表中
                mSheet.addCell(label);
            }

            DbHelper dbHelper = new DbHelper(context);
            List<Humans> humansList = dbHelper.getAllHumans();
            for (int i = 0; i < humansList.size(); i++) {
                Humans human = humansList.get(i);
                Label yearCell = new Label(0, i + 1, human.getYear());
                String monthStr = "-1".equals(human.getMonth()) ? "当天" : human.getMonth() + "月";
                Label monthCell = new Label(1, i + 1, monthStr);
                Label humanNumCell = new Label(2, i + 1, String.valueOf(human.getHumanNums()));

                mSheet.addCell(yearCell);
                mSheet.addCell(monthCell);
                mSheet.addCell(humanNumCell);
            }
            wwb.write();
            wwb.close();
            usbHelper.saveSDFileToUsb(mFile, usbHelper.getCurrentFolder(), listener);
        } catch (Exception e) {
            Log.e(TAG, "create sheet excel failed");
            return false;
        }
        return true;
    }

    public String getCell(int index, int i) {
        WritableCell cell = mSheet.getWritableCell(index, i);
        return cell.getContents();
    }

    public WritableCellFormat getHeader() {
        WritableFont font = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD);// 定义字体
        try {
            font.setColour(Colour.BLUE);// 蓝色字体
        } catch (WriteException e1) {
            e1.printStackTrace();
        }
        WritableCellFormat format = new WritableCellFormat(font);
        try {
            format.setAlignment(Alignment.CENTRE);// 左右居中
            format.setVerticalAlignment(VerticalAlignment.CENTRE);// 上下居中
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    /**
     * 获取SD可用容量
     */
    private long getAvailableStorage() {
        StatFs statFs = new StatFs(root);
        long blockSize = statFs.getBlockSize();
        long availableBlocks = statFs.getAvailableBlocks();
        long availableSize = blockSize * availableBlocks;
        // Formatter.formatFileSize(context, availableSize);
        return availableSize;
    }
}
